<?php
$df = $AppUI->getPref('SHDATEFORMAT');

$status_id = intval( dPgetParam( $_POST, 'status_id', 0 ) );
$customer = intval( dPgetParam( $_POST, 'customer', 0 ) );
$item_type = intval( dPgetParam( $_POST, 'item_type', 0 ) );
$from_date = dPgetParam( $_POST, 'from_date', '' );
$to_date = dPgetParam( $_POST, 'to_date', '' );

$q = new DBQuery();
$q->dropTemp('shipping_types, itemdetails, wunit, qunit, vunit, pitems, container');
$q->exec();
$q->clear();

$q->createTemp('shipping_types');
$q->addQuery('type_id, name as shipping_type_name');
$q->addTable('md_type');
$q->addWhere("type = 'ShippingType'");
$q->exec();
$q->clear();

$q->createTemp('itemdetails');
$q->addTable('items');
$q->addQuery('items.*, t.name as type_name');
$q->innerJoin('md_type', 't', 't.type_id = items.item_type1');
if ($item_type > 0) {
	$q->addWhere("item_type1 = $item_type");
}
$q->exec();
$q->clear();

$q->createTemp('wunit');
$q->addTable('md_unit');
$q->addQuery('unit_id as wunit_id, unit_name as wunit_name');
$q->exec();
$q->clear();

$q  = new DBQuery;
$q->createTemp('qunit');
$q->addTable('md_unit');
$q->addQuery('unit_id as qunit_id, unit_name as qunit_name');
$q->exec();
$q->clear();

// Volumn Unit
$q  = new DBQuery;
$q->createTemp('vunit');
$q->addTable('md_unit');
$q->addQuery('unit_id as vunit_id, unit_name as vunit_name');
$q->exec();
$q->clear();

// Container type
$q  = new DBQuery;
$q->createTemp('container');
$q->addTable('md_type');
$q->addQuery('type_id as container_id, name as container_name');
$q->exec();
$q->clear();

$q->createTemp('pitems');
$q->addTable('project_items');
$q->innerJoin('itemdetails', 'i', 'pitem_item_id = i.item_id');
$q->leftJoin('wunit', 'wu', 'i.item_weight_unit = wu.wunit_id');
$q->leftJoin('qunit', 'qu', 'i.item_quantity_unit = qu.qunit_id');
$q->leftJoin('vunit', 'vu', 'i.item_volume_unit = vu.vunit_id');
$q->leftJoin('container', 'con', 'pitem_container = con.container_id');
$q->leftJoin('md_currency', 'currency', 'pitem_currency = currency.currency_id');
$q->addQuery('pitem_id, pitem_project,pitem_item_id,pitem_BL_AW,pitem_invoice_no,pitem_currency,pitem_invoice_amount,pitem_PO_no,pitem_CO_no,pitem_contract_no,pitem_quantity,pitem_weight,pitem_volume,pitem_container,pitem_package,pitem_stamp_time,' .
		'DATE_FORMAT(pitem_stamp_date, \'%Y-%m-%dT%T.000\') as pitem_stamp_date,' .
		'pitem_customs_dec_no,' .
		'DATE_FORMAT(pitem_custom_date, \'%Y-%m-%dT%T.000\') as pitem_custom_date,' .
		'DATE_FORMAT(pitem_ETA_date, \'%Y-%m-%dT%T.000\') as pitem_ETA_date,' .
		'DATE_FORMAT(pitem_delivery_date, \'%Y-%m-%dT%T.000\') as pitem_delivery_date,' .
		'pitem_delivery_location,pitem_inspection,pitem_unloading,pitem_remarks, ' .
		'i.item_name, i.item_code, wu.wunit_name as weight_name, qu.qunit_name as quantity_name, vu.vunit_name as volume_name, container_name, currency_name, currency_abbreviation');
$q->exec();
$q->clear();

$q->addTable('projects');
$q->innerJoin('pitems', 'pi', 'pitem_project = project_id');
$q->addJoin('shipping_types', 'stypes', 'projects.project_shipping_type = stypes.type_id');
$q->innerJoin('companies', 'c', 'company_id = project_company');
$q->addJoin('suppliers', 'sup', 'sup_id = project_shipper');
$q->innerJoin('md_type', 't', 'project_rtype = t.type_id');
$q->innerJoin('md_status', 's', 'project_rstatus = s.status_id');
$q->addQuery('project_code, project_name, project_iso_code, shipping_type_name, t.name as type, s.status_name as status, company_name, ' .
			' pi.*, sup.sup_name as shipper_name');
if ($customer > 0) {
	$q->addWhere("project_company = $customer");
}
if ($status_id > 0) {
	$q->addWhere("project_rstatus = $status_id");
}
if ($from_date) {
	$q->addWhere("project_date >= $from_date");
}
if ($to_date) {
	$q->addWhere("project_date <= $to_date");
}
$q->addOrder('project_id');
$result = $q->loadList();
/*
// Get all fields names in table "name_list" in database "tutorial".
$fields = mysql_list_fields(tutorial, $table);

// Count the table fields and put the value into $columns.
$columns = mysql_num_fields($fields);

// Put the name of all fields to $out.
for ($i = 0; $i < $columns; $i++) {
	$l = mysql_field_name($fields, $i);
	$out .= '"' . $l . '",';
}
$out .= "n";
*/
/* trangdt comment
// Add all values in the table to $out.
$out = '';
foreach($result as $row) {
	foreach($row as $k => $v) {
		$out .= '"' . $v . '",';
	}
	$out .= "\n";
}

// Open file export.csv.
$f = fopen('files/temp/references.csv', 'w');

// Put all values from $out to export.csv.
//$out = chr(255).chr(254).mb_convert_encoding($out, 'UTF-16LE', 'UTF-8');
fputs($f, $out);
fclose($f);

header('Content-type: application/csv');
header('Content-Disposition: attachment; filename="export.csv"');
readfile('files/temp/references.csv');
*/
// trangdt add

//echo'<pre>';print_r( $result[0]);echo'</pre>';

include_once(DP_BASE_DIR."/modules/reports/xtpl/xtemplate.class.php");

$xtpl = new XTemplate(DP_BASE_DIR."/modules/reports/Reference.xtpl");
$size = count($result);
// simple replace
$xtpl->assign('FROMDATE', $from_date);
$xtpl->assign('TODATE', $to_date);
$xtpl->assign('SIZE', $size+10);

// parse block1
$xtpl->parse('main.block1');

for($i=0; $i<$size-1; $i++) {
	$ref_line = $result[$i];
	$xtpl->assign('DATA',$ref_line);
	$xtpl->assign('pitem_stamp_date',replace($ref_line['pitem_stamp_date']));
	$xtpl->assign('pitem_custom_date',replace($ref_line['pitem_custom_date']));
	$xtpl->assign('pitem_ETA_date',replace($ref_line['pitem_ETA_date']));
	$xtpl->assign('pitem_delivery_date',replace($ref_line['pitem_delivery_date']));
	$xtpl->parse('main.row');
}
$ref_line = $result[$size-1];
$xtpl->assign('DATA',$result[$size-1]);
$xtpl->assign('pitem_stamp_date',replace($ref_line['pitem_stamp_date']));
$xtpl->assign('pitem_custom_date',replace($ref_line['pitem_custom_date']));
$xtpl->assign('pitem_ETA_date',replace($ref_line['pitem_ETA_date']));
$xtpl->assign('pitem_delivery_date',replace($ref_line['pitem_delivery_date']));
$xtpl->parse('main.lastrow');
$xtpl->parse('main');
$out = $xtpl->out('main');

$temp_dir = dPgetConfig( 'root_dir' )."/files/temp";
if ($fp = fopen( "$temp_dir/temp$AppUI->user_id.xls", 'wb' )) {
	fwrite( $fp, $out);
	fclose( $fp );
	header("Content-Type: application/vnd.ms-excel; charset=UTF-8");
	header("Content-Disposition: inline; filename=\"reference.xls\"");
	readfile("$temp_dir/temp$AppUI->user_id.xls");
}
function replace($date){
	if($date != null){		
		return 	'<Data ss:Type="DateTime">'.$date.'</Data>';
	}
	else{			
		return $date;
	}	  
}
?>
